﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml;
using System.Web.Caching;
using System.Data;
using Teradata.Client.Provider;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Caching
/// </summary>
/// 
public class Caching
{
    public Caching()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public static DateTime Started = DateTime.Now;

    public static string CachingText
    {
        get {
            return CookieMonstar.getCookie("CachingText");
        }
        set { 
            CookieMonstar.setCookie("CachingText", value, DateTime.Now.AddMinutes(1));
            System.Threading.Thread.SpinWait(100500);  
        }
    }

    public DateTime CacheDate { get { return (DateTime)HttpContext.Current.Application["CacheDate"]; } }

    static string[] _Connections = { "TDConnString", "EDW_GROUP_ID" };
    static bool bWorkingDisconnected = Convert.ToBoolean(ConfigurationManager.AppSettings["WorkingDisconnected"]);

    public delegate void RefreshCache(String key, Object item, CacheItemRemovedReason reason);

    public DateTime TeradataDate
    {
        //Get the most recent Sales date from Teradata
        get
        {
            DataSet ds = new DataSet();
            string sSQL = SQLFiles.ConstructSQL("STARTDATE", ""); //"SELECT	MAX( B.START_DT  ) FROM 	MIS.ACC_RAW B  ";
            //exec sql -> dataset tables
            using (TdConnection cn = new TdConnection(ConfigurationManager.ConnectionStrings[_Connections[1]].ConnectionString))
            {
                cn.Open();
                using (TdDataAdapter da = new TdDataAdapter(sSQL, cn))
                {
                    da.SelectCommand.CommandTimeout = 0;
                    da.Fill(ds);
                }
            }


            return (DateTime)ds.Tables[0].Rows[0][0];
        }

    }

    public void RefreshTheCache(String psDatasetName, Object item, CacheItemRemovedReason reason)
    {
        RestoreCache(psDatasetName);
    }

    public DataSet RestoreCache(String psDatasetName)
    {
        CachingText = "RESTORING " + psDatasetName;
        FileStream fs = new FileStream(string.Format("{1}SQL\\Data\\{0}.bin", psDatasetName, ConfigurationManager.AppSettings["RootPath"]), FileMode.Open);

        System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bFormat =
                new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

        if (HttpContext.Current != null)
            HttpContext.Current.Cache.Insert(psDatasetName, (DataSet)bFormat.Deserialize(fs),
                 new CacheDependency(string.Format("{0}SQL\\Data\\ServerSalesDate.txt", ConfigurationManager.AppSettings["RootPath"])), DateTime.Today.AddDays(1).AddSeconds(-1), Cache.NoSlidingExpiration,
                 CacheItemPriority.Default, new CacheItemRemovedCallback(RefreshTheCache));

        if (HttpContext.Current != null)
            return (DataSet)HttpContext.Current.Cache[psDatasetName];
        else
            return new DataSet();
    }


    public DataSet Get_DataSet(string psSQLFiles, int pnConnName, RefreshCache handler, bool pbFromFile, string psSubset)
    {
        DateTime startTime = DateTime.Now;
        //

        bool bFromFile;
        //read sql from file(s)
        //string[] saSQLFiles = psSQLFiles.Split(',');
        //if (saSQLFiles.Length == 1) saSQLFiles = psSQLFiles.Split('+');
        //string sDatasetName = saSQLFiles[0] + (psSubset.Length > 0 ? "_" + psSubset : "");
        string sDatasetName = (new SQLFiles()).DataSetName(psSQLFiles, psSubset);
        CachingText = "=" + sDatasetName + " STARTED";

        DataSet tmpDataSet = new DataSet(sDatasetName);

        if (bWorkingDisconnected)
            bFromFile = true;
        else
        {
            bFromFile = !(bool)HttpContext.Current.Application["Cache Is Stale"];
        }
        if (bFromFile)
        {
            tmpDataSet = RestoreCache(sDatasetName);
        }
        else
        {
            //Get the dataset from Teradata
            String sSQL = SQLFiles.ConstructSQL(psSQLFiles, psSubset);
            //String sSQL = string.Empty;
            //if (psSQLFiles.Split('+').Length == 1)
            //    for (int i = 0; i < saSQLFiles.Length; i++)
            //        sSQL += System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(string.Format("SQL\\{0}.sql", saSQLFiles[i])));
            //else
            //{
            //    string sTemplate = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(string.Format("SQL\\{0}.sql", saSQLFiles[0])));
            //    string sDetails = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(string.Format("SQL\\{0}.sql", saSQLFiles[1])));
            //    sSQL = String.Format(sTemplate, sDetails);
            //}

            //if (psSubset.Length > 0) sSQL = string.Format(sSQL, psSubset);

            tmpDataSet.ExtendedProperties["TimeStamp"] = DateTime.Now;
            tmpDataSet.ExtendedProperties["DataSetID"] = Guid.NewGuid();
            tmpDataSet.ExtendedProperties["Company"] = "at&t";
            CachingText = "=" + sDatasetName + " READING";



            using (TdConnection cn = new TdConnection(ConfigurationManager.ConnectionStrings[_Connections[pnConnName]].ConnectionString))
            {
                cn.Open();
                using (TdDataAdapter da = new TdDataAdapter(sSQL, cn))
                {
                    da.SelectCommand.CommandTimeout = 0;
                    da.Fill(tmpDataSet);
                }
            }

            NameRollupTables(tmpDataSet);

            CachingText = "=" + sDatasetName + " CACHING";
            //Cache the data 
            //HttpContext.Current.Application.Add(sDatasetName, tmpDataSet);
            HttpContext.Current.Cache.Insert(sDatasetName, tmpDataSet, new CacheDependency(HttpContext.Current.Server.MapPath("SQL\\Data\\ServerSalesDate.txt")),
                DateTime.Today.AddDays(1).AddSeconds(-1), Cache.NoSlidingExpiration,
                CacheItemPriority.Default, new CacheItemRemovedCallback(handler));

            CachingText = "=" + sDatasetName + " SAVING";
            SerializeData(sDatasetName, tmpDataSet);
        }
        //record the caching event
        DateTime stopTime = DateTime.Now;
        TimeSpan duration = stopTime - startTime;
        HttpContext.Current.Application[string.Format("{0} Cached On", sDatasetName)] = startTime.ToString();
        HttpContext.Current.Application[string.Format("{0} Sizes", sDatasetName)] = tmpDataSet.Tables[0].Rows.Count;
        HttpContext.Current.Application[string.Format("{0} Times", sDatasetName)] = startTime.ToString() + " " + duration.ToString();
        return tmpDataSet;
    }

    private static void NameRollupTables(DataSet tmpDataSet)
    {

        //name the data tables after the rollup type (if it's one of 14 rollup tables
        for (int i = 0; i < tmpDataSet.Tables.Count; i++)
        {
            //get value of first col of first row 
            if (tmpDataSet.Tables[i].Rows.Count > 0 && tmpDataSet.Tables[i].Rows[0].Table.Columns.Contains("Rollup_Type"))
            {
                string sTableName = (string)tmpDataSet.Tables[i].Rows[0]["Rollup_Type"];
                tmpDataSet.Tables[i].TableName = sTableName;
            }
        }
    }

    private static void SerializeData(string sDatasetName, DataSet tmpDataSet)
    {

        //Serialize the dataset for persistent cache layer
        //This way, in our loads and refreshes we can check if file is current and use it
        //instead of hitting Teradata to calculate the data again
        //
        FileStream fs = new FileStream(string.Format(HttpContext.Current.Server.MapPath("SQL\\Data\\{0}.bin"), sDatasetName), FileMode.Create);

        System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bFormat =
            new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

        bFormat.Serialize(fs, tmpDataSet);
        fs.Close();


    }


    public DataSet Get_DataSet_From_SQLServer(string psSQLFiles, int pnConnName, RefreshCache handler, bool pbFromFile, string psSubset)
    {
        DateTime startTime = DateTime.Now;
        //

        bool bFromFile;
        //read sql from file(s)
        //string[] saSQLFiles = psSQLFiles.Split(',');
        //if (saSQLFiles.Length == 1) saSQLFiles = psSQLFiles.Split('+');
        //string sDatasetName = saSQLFiles[0] + (psSubset.Length > 0 ? "_" + psSubset : "");
        string sDatasetName = (new SQLFiles()).DataSetName(psSQLFiles, psSubset);
        CachingText = "=" + sDatasetName + " STARTED";

        DataSet tmpDataSet = new DataSet(sDatasetName);

        if (bWorkingDisconnected)
            bFromFile = true;
        else
        {
            bFromFile = !(bool)HttpContext.Current.Application["Cache Is Stale"];
        }

        bFromFile = sDatasetName != "ACC_DATA";
        if (bFromFile)
        {
            tmpDataSet = RestoreCache(sDatasetName);
        }
        else
        {
            //Get the dataset from Teradata
            String sSQL = SQLFiles.ConstructSQL(psSQLFiles, psSubset);
            //String sSQL = string.Empty;
            //if (psSQLFiles.Split('+').Length == 1)
            //    for (int i = 0; i < saSQLFiles.Length; i++)
            //        sSQL += System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(string.Format("SQL\\{0}.sql", saSQLFiles[i])));
            //else
            //{
            //    string sTemplate = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(string.Format("SQL\\{0}.sql", saSQLFiles[0])));
            //    string sDetails = System.IO.File.ReadAllText(HttpContext.Current.Server.MapPath(string.Format("SQL\\{0}.sql", saSQLFiles[1])));
            //    sSQL = String.Format(sTemplate, sDetails);
            //}

            //if (psSubset.Length > 0) sSQL = string.Format(sSQL, psSubset);

            tmpDataSet.ExtendedProperties["TimeStamp"] = DateTime.Now;
            tmpDataSet.ExtendedProperties["DataSetID"] = Guid.NewGuid();
            tmpDataSet.ExtendedProperties["Company"] = "at&t";
            CachingText = "=" + sDatasetName + " READING";

            string[] saTablesSQL = sSQL.Split(';');

            for (int i = 0; i < saTablesSQL.Length; i++)
            {
                string sTableSQL = saTablesSQL[i].Trim();
                if (sTableSQL.Length > 2)
                {
                    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString))
                    {
                        cn.Open();
                        using (SqlDataAdapter da = new SqlDataAdapter(sTableSQL, cn))
                        {
                            da.SelectCommand.CommandTimeout = 0;
                            DataTable dt = new DataTable();
                            da.Fill(dt);
                            tmpDataSet.Tables.Add(dt);
                        }
                    }
                }
            }



            //NameRollupTables(tmpDataSet);

            CachingText = "=" + sDatasetName + " CACHING";
            //Cache the data 
            //HttpContext.Current.Application.Add(sDatasetName, tmpDataSet);
            HttpContext.Current.Cache.Insert(sDatasetName, tmpDataSet, new CacheDependency(HttpContext.Current.Server.MapPath("SQL\\Data\\ServerSalesDate.txt")),
                DateTime.Today.AddDays(1).AddSeconds(-1), Cache.NoSlidingExpiration,
                CacheItemPriority.Default, new CacheItemRemovedCallback(handler));

            CachingText = "=" + sDatasetName + " SAVING";
            SerializeData(sDatasetName, tmpDataSet);
        }
        //record the caching event
        DateTime stopTime = DateTime.Now;
        TimeSpan duration = stopTime - startTime;
        HttpContext.Current.Application[string.Format("{0} Cached On", sDatasetName)] = startTime.ToString();
        HttpContext.Current.Application[string.Format("{0} Sizes", sDatasetName)] = tmpDataSet.Tables[0].Rows.Count;
        HttpContext.Current.Application[string.Format("{0} Times", sDatasetName)] = startTime.ToString() + " " + duration.ToString();
        return tmpDataSet;
    }

    public DataSet Get_DataSet_SQLServer(string psSQLFiles, int pnConnName, RefreshCache handler, bool pbIsIncremental, string psSubset)
    {
        DateTime startTime = DateTime.Now;
        //

        bool bFromFile;
        //read sql from file(s)
        string sDatasetName = (new SQLFiles()).DataSetName(psSQLFiles, psSubset);
        //string[] saSQLFiles = psSQLFiles.Split(',');
        //if (saSQLFiles.Length == 1) saSQLFiles = psSQLFiles.Split('+');
        //string sDatasetName = saSQLFiles[0] + (psSubset.Length > 0 ? "_" + psSubset : "");

        DataSet tmpDataSet = new DataSet(sDatasetName);

        if (bWorkingDisconnected)
            bFromFile = true;
        else
        {
            bFromFile = !(bool)HttpContext.Current.Application["Cache Is Stale"];
        }
        if (bFromFile)
        {
            tmpDataSet = RestoreAllCachesFromSQLServer(sDatasetName); // has to do 1 for each table in dataset!!!
        }
        else
        {
            String sSQL = SQLFiles.ConstructSQL(psSQLFiles, psSubset);

            ////Get the dataset from Teradata

            //            if (psSubset.Length > 0) sSQL = string.Format(sSQL, psSubset);

            string sConn = ConfigurationManager.ConnectionStrings[_Connections[pnConnName]].ConnectionString;
            string[] saTablesSQL = sSQL.Split(';');

            for (int i = 0; i < saTablesSQL.Length; i++)
            {
                string sTableSQL = saTablesSQL[i].Trim();
                if (sTableSQL.Length > 2)
                {
                    string sTableName = string.Empty;
                    if (sDatasetName.Contains("_STATS_") || sDatasetName.Contains("_PROPERTIES") || sDatasetName.Contains("_ServiceOrderPullAll") || sDatasetName.Contains("SCORE_MTD"))
                        sTableName = sDatasetName + "." + GetRollupTableName(sTableSQL);
                    else
                        sTableName = sDatasetName + "." + GetTableNameFromSQLFirstColumn(sTableSQL);

                    DataTable dt;

                    if (ConfigurationManager.AppSettings[sDatasetName] != null && ConfigurationManager.AppSettings[sDatasetName].Length > 0)
                        pbIsIncremental = Convert.ToBoolean(ConfigurationManager.AppSettings[sDatasetName]);

                    if (pbIsIncremental)
                        dt = (new BulkLoad()).LoadTableFromTeradata(sConn, sTableSQL, sTableName, pbIsIncremental);
                    else
                        dt = (new BulkLoad()).LoadTableFromTeradata(sConn, sTableSQL, sTableName);
                    ////Cache the data 
                    ////HttpContext.Current.Application.Add(sTableName, dt);
                    //HttpContext.Current.Cache.Insert(sTableName, dt, new CacheDependency(HttpContext.Current.Server.MapPath("SQL\\Data\\ServerSalesDate.txt")),
                    //    DateTime.Today.AddDays(1).AddSeconds(-1), Cache.NoSlidingExpiration,
                    //    CacheItemPriority.Default, new CacheItemRemovedCallback(handler));
                }
            }




            //SerializeData(sDatasetName, tmpDataSet);
        }
        //record the caching event
        DateTime stopTime = DateTime.Now;
        TimeSpan duration = stopTime - startTime;
        //HttpContext.Current.Application[string.Format("{0} Cached On", sDatasetName)] = startTime.ToString();
        //HttpContext.Current.Application[string.Format("{0} Sizes", sDatasetName)] = tmpDataSet.Tables[0].Rows.Count;
        //HttpContext.Current.Application[string.Format("{0} Times", sDatasetName)] = startTime.ToString() + " " + duration.ToString();
        return tmpDataSet;
    }

    private string GetTableNameFromSQLFirstColumn(string psTableSQL)
    {
        string[] saSplit = new string[] { "SELECT", "\t\r\n", "\r\n", "," };
        string[] sa1 = psTableSQL.Split(saSplit, StringSplitOptions.RemoveEmptyEntries);
        return sa1[1].Trim();
    }

    private string GetRollupTableName(string psTableSQL)
    {
        string[] saSplit = new string[] { " ", "\t\r\n", "\r\n", "," };
        string[] sa1 = psTableSQL.Split(saSplit, StringSplitOptions.RemoveEmptyEntries);
        return sa1[1].Trim();
    }

    public void RefreshTheCacheFromSQLServer(String psDatasetName, Object item, CacheItemRemovedReason reason)
    {
        RestoreCacheFromSQLServer(psDatasetName);
    }


    public DataSet RestoreAllCachesFromSQLServer(String psDatasetName)
    {
        //Get the schema's table names
        String sSQL = string.Format("SELECT name FROM sys.tables WHERE (schema_id = (SELECT schema_id FROM sys.schemas WHERE (name = '{0}')))", psDatasetName);
        DataSet ds = new DataSet();
        DataTable dtTableNames = new DataTable();
        string sSQLServerCn = ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString;


        using (SqlConnection cnSQLServer = new SqlConnection(sSQLServerCn))
        {
            cnSQLServer.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(sSQL, cnSQLServer))
            {
                da.SelectCommand.CommandTimeout = 0;
                da.Fill(dtTableNames);
            }
        }
        for (int i = 0; i < dtTableNames.Rows.Count; i++)
        {
            String sTableName = dtTableNames.Rows[i]["Name"].ToString();
            CachingText = sTableName + "=" + psDatasetName;
            ds.Tables.Add(RestoreCacheFromSQLServer(psDatasetName + "." + sTableName));
        }

        return ds;
    }


    public DataTable RestoreCacheFromSQLServer(String psTableName)
    {
        DataTable dtSQLServer = new DataTable();
        return dtSQLServer;
        try
        {

            //Read each table and add it to the dataset
            string sSQLServerCn = ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString;


            using (SqlConnection cnSQLServer = new SqlConnection(sSQLServerCn))
            {
                cnSQLServer.Open();
                using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM " + psTableName, cnSQLServer))
                {
                    da.SelectCommand.CommandTimeout = 0;
                    da.Fill(dtSQLServer);
                }
            }

            //FileStream fs = new FileStream(string.Format("{1}SQL\\Data\\{0}.bin", psDatasetName, ConfigurationManager.AppSettings["RootPath"]), FileMode.Open);

            //System.Runtime.Serialization.Formatters.Binary.BinaryFormatter bFormat =
            //        new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();

            if (HttpContext.Current != null)
            {
                HttpContext.Current.Cache.Insert(psTableName, dtSQLServer,
                     new CacheDependency(string.Format("{0}SQL\\Data\\ServerSalesDate.txt", ConfigurationManager.AppSettings["RootPath"])), DateTime.Today.AddDays(1).AddSeconds(-1), Cache.NoSlidingExpiration,
                     CacheItemPriority.Default, new CacheItemRemovedCallback(RefreshTheCacheFromSQLServer));
            }
        }

        finally { }

        return dtSQLServer;
    }
}